﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using motelmanagement.common;
using System.Data;
using System.Data.OleDb;
namespace motelmanagement.dao
{
    class BookingDAO
    {
        SqlUtil sqlUtil = new SqlUtil();
        //tra ve tat ca cac record trong table
        public DataTable getALLBooking()
        {
            string strSql = "SELECT Booking.booking_id, Booking.cus_id,Customer.cus_name";
            strSql += ", Customer.cus_idnumber, Customer.cus_phone, Booking.booking_day";
            strSql += ", Booking.booking_time, Room_category.room_cate_id, Room_category.room_cate_name";
            strSql += ", Room_category.room_cate_people, Room_category.room_cate_bed";
            strSql += ", Booking.booking_pay FROM (Customer INNER JOIN Booking ON Customer.cus_id = Booking.cus_id)";
            strSql += "INNER JOIN Room_category ON Booking.room_cate_id = Room_category.room_cate_id";
            OleDbCommand cmd = new OleDbCommand(strSql);
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        //tra ve 1 record trong table
        public DataTable getBooking(int bookingID)
        {
            string strSql = "SELECT Booking.booking_id, Booking.cus_id,Customer.cus_name";
            strSql += ", Customer.cus_idnumber, Customer.cus_phone, Booking.booking_day";
            strSql += ", Booking.booking_time, Room_category.room_cate_id, Room_category.room_cate_name";
            strSql += ", Room_category.room_cate_people, Room_category.room_cate_bed";
            strSql += ", Booking.booking_pay FROM (Customer INNER JOIN Booking ON Customer.cus_id = Booking.cus_id)";
            strSql += " INNER JOIN Room_category ON Booking.room_cate_id = Room_category.room_cate_id";
            strSql += " WHERE Booking.booking_id=@id";
            OleDbCommand cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id", SqlDbType.Int).Value = bookingID;
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        // xoa 1 row trong table
        public void deleteBooking(int bookingID)
        {
            string strSql = "DELETE FROM Booking WHERE booking_id = @id";
            OleDbCommand cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id", SqlDbType.Int).Value = bookingID;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        //cap nhat 1 row trong table
        public void updateBooking(int bookingID, string bookingDay, string bookingTime, int roomCategoryID, int bookingPay)
        {
            string strSql = "UPDATE Booking SET Booking.booking_day = @bkDay";
            strSql += ", Booking.booking_time = @bkTime, Booking.room_cate_id = @id";
            strSql += ", Booking.booking_pay = @bkPay WHERE booking_id = @bkID";
            OleDbCommand cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("bkDay", SqlDbType.Text).Value = bookingDay;
            cmd.Parameters.Add("bkTime", SqlDbType.Text).Value = bookingTime;
            cmd.Parameters.Add("id", SqlDbType.Int).Value = roomCategoryID;
            cmd.Parameters.Add("bkPay", SqlDbType.Int).Value = bookingPay;
            cmd.Parameters.Add("bkID", SqlDbType.Int).Value = bookingID;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        //insert 1 row trong table
        public void insertBooking(int customerID, string bookingDay, string bookingTime, int roomCategoryID, int bookingPay)
        {
            string strSql = "INSERT INTO Booking ( cus_id, booking_day, booking_time, room_cate_id, booking_pay )";
            strSql += " VALUES(@cusID,@bkDay,@bkTime,@roomcateID,@bkDay)";
            OleDbCommand cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("cusID", SqlDbType.Int).Value = customerID;
            cmd.Parameters.Add("bkDay", SqlDbType.Text).Value = bookingDay;
            cmd.Parameters.Add("bkTime", SqlDbType.Text).Value = bookingTime;
            cmd.Parameters.Add("roomcateid", SqlDbType.Int).Value = roomCategoryID;
            cmd.Parameters.Add("bkPay", SqlDbType.Int).Value = bookingPay;
            sqlUtil.ExecuteNoneQuery(cmd);
        }

        public DataRow NewRow()
        {
            return sqlUtil.NewRow();
        }

        public void Add(DataRow row)
        {
            sqlUtil.Rows.Add(row);
        }

        public bool Save()
        {
            return (sqlUtil.ExecuteNoneQuery() > 0);
        }
    }
}
